<%@ page import="java.sql.*" %> 
<%@ page import="java.lang.*" %> 
<%@ page import="java.io.*" %> 
<%@ page language="java" import="net.sf.json.JSONArray" %>
<%@ page language="java" import="net.sf.json.JSONObject" %> 
<%
try {
String connectionURL = "jdbc:mysql://webproject2.cse.ust.hk/BookwarmMobile?user=BookwarmMobile&password=bookwarm";

Connection connection = null;

Class.forName("com.mysql.jdbc.Driver").newInstance();

connection = DriverManager.getConnection(connectionURL);

if(!connection.isClosed()){
String newISBN = null; 
Statement statement = null;
Statement stm2 = null;
ResultSet rs = null; 
ResultSet rs2 = null;
String title = null;
String edition = null;
String courses = null;
String authors = null;
String publisher = null;
String isbn = null;
String copyID = null;
String copyCondition = null;
String copyPrice = null;
String copyAvail = null;
JSONArray arrayObj=new JSONArray();
JSONArray copyLikeNew = new JSONArray();
JSONArray copyGood = new JSONArray();
JSONArray copyFair = new JSONArray();

newISBN = request.getParameter("input");

statement = connection.createStatement();
rs = statement.executeQuery("SELECT * FROM Books where Books.ISBN = '" + newISBN + "'");

while (rs.next()) {
title = rs.getString("Books.BookTitle");
edition = rs.getString("Books.Edition");
courses = rs.getString("Books.Courses");
authors = rs.getString("Books.Authors");
publisher = rs.getString("Books.Publisher");
isbn = rs.getString("Books.ISBN");

stm2 = connection.createStatement();
rs2 = stm2.executeQuery("SELECT * FROM BookCopies where BookCopies.ISBN = '" + isbn + "' AND Availability <> 'N/A'");

while(rs2.next()) {
copyID = rs2.getString("BookCopies.CopyID");
copyCondition = rs2.getString("BookCopies.CopyCondition");
copyPrice = rs2.getString("BookCopies.CopyPrice");
copyAvail = rs2.getString("BookCopies.Availability");
	
JSONObject newCopy = new JSONObject();
newCopy.put("CopyID", copyID);
newCopy.put("Condition", copyCondition);
newCopy.put("Price", copyPrice);
newCopy.put("Availability", copyAvail);
	
if(copyCondition.equals("Like New")) {
copyLikeNew.add(newCopy);

}
else if(copyCondition.equals("Good")) {
copyGood.add(newCopy);

}
else {
copyFair.add(newCopy);

}

}

JSONObject first =  new JSONObject();
first.put("Title", title);
first.put("Edition", edition);
first.put("Courses", courses);
first.put("Authors", authors);
first.put("Publisher", publisher);
first.put("ISBN", isbn);
first.put("CopyLikeNew", copyLikeNew);
first.put("CopyGood", copyGood);
first.put("CopyFair", copyFair);

arrayObj.add(first);

} 

out.print(arrayObj);

}

connection.close();

}
catch(Exception ex){
out.print(ex);

}
%>